﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class EspecieItemData
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim valida As String = Session("logado")

        'lblMsg.Visible = False

        'Faz com que ao sair do aplicativo, limpe o buffer e não podendo entrar na página através do butão voltar do Browser.
        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)
        'Fim

        Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))

        If Not Page.IsPostBack Then

            If (Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Alterar | Excluir
                CarregaDados()
            End If

        End If

    End Sub

    Private Sub CarregaDados()

        txtDscEspecieItem.Enabled = False


        If (Session("strOperacao") = "E") Then ' Excluir
            txtDscEspecieItem.Enabled = False
        Else
            txtDscEspecieItem.Enabled = True
        End If

        lblCodEspecieItem.Text = Session("strCodigoEspecieItem").ToString

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT DscEspecieItem" & _
                     " FROM especieitem" & _
                     " WHERE CodEspecieItem = '" & Session("strCodigoEspecieItem").ToString & "';"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()
                    txtDscEspecieItem.Text = theSqlDataReader(0)
                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Function ValidaCampos() As Boolean

        ValidaCampos = True

        If (Trim(txtDscEspecieItem.Text) = "") Then
            ValidaCampos = False
            lblMsg.Text = "Descrição da Especie do Item deve ser preenchido."
            Exit Function
        End If

    End Function


    Private Function VerificaDado(ByVal strConn As String) As Boolean

        'VerificaDado = False

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim theQueryString As String = "SELECT CodigoDoPaisISO" & _
        '             " FROM v_Holding" & _
        '             " WHERE CodigoDoPaisISO = '" & Session("CodigoDoPaisISO") & "'" & _
        '             " GROUP BY CodigoDoPaisISO"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        If (theSqlDataReader.Read()) Then
        '            VerificaDado = True
        '            lblMsg.Text = "Exclusão não permitida, existe relação do País com Holding."
        '        End If
        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        VerificaDado = True
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

    End Function

    Private Function GetNextEspecieDoItem() As Boolean

        GetNextEspecieDoItem = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Try
                Dim theQueryString As String = ""

                theQueryString = "SELECT MAX(CodEspecieItem) AS MAX_CodEspecieItem" & _
                                 " FROM especieitem"

                Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()

                theSqlCommand.CommandText = theQueryString
                theSqlConnection.Open()

                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()
                    If Not IsDBNull(theSqlDataReader(0)) Then
                        lblCodEspecieItem.Text = theSqlDataReader(0)
                        lblCodEspecieItem.Text = (CInt(lblCodEspecieItem.Text) + 1)
                    End If
                Loop

                theSqlConnection.Close()

                GetNextEspecieDoItem = True

            Catch ex As Exception
                theSqlConnection.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function IncluiEspecieDoItem(ByVal strConn As String) As Boolean

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nDataCreated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nDataCreated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nDataCreated)) Then
            nDataCreated = "'" & FormatMediumDate(nDataCreated) & "'"
        Else
            nDataCreated = "Null"
        End If

        IncluiEspecieDoItem = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "INSERT INTO especieitem" & _
                     " (CodEspecieItem, DscEspecieItem," & _
                     " CreateUSer, Created)" & _
                     " VALUES (" & Trim(lblCodEspecieItem.Text) & ", '" & Trim(txtDscEspecieItem.Text) & "'," & _
                      " '" & Session("sNM_USERNICKNAME") & "', " & nDataCreated & ");"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                IncluiEspecieDoItem = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function AlteraEspecieDoItem(ByVal strConn As String) As Boolean

        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nDataCreated As String

        AlteraEspecieDoItem = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nDataCreated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nDataCreated)) Then
            nDataCreated = "'" & FormatMediumDate(nDataCreated) & "'"
        Else
            nDataCreated = "Null"
        End If

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "UPDATE especieitem SET" & _
                     " DscEspecieItem = '" & Trim(txtDscEspecieItem.Text) & "'," & _
                     " LastUser = '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " LastUpdated = " & nDataCreated & "" & _
                     " WHERE CodEspecieItem = " & lblCodEspecieItem.Text & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                AlteraEspecieDoItem = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function ExcluiEspecieDoItem(ByVal strConn As String) As Boolean

        ExcluiEspecieDoItem = False

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM especieitem " & _
                     "WHERE CodEspecieItem = " & lblCodEspecieItem.Text & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                ExcluiEspecieDoItem = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function



    Protected Sub btnGravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGravar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.Text = ""

        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (ValidaCampos() = True) Then
                Exit Sub
            End If

            If Not (GetNextEspecieDoItem() = True) Then
                Exit Sub
            End If

            If Not (IncluiEspecieDoItem(ConnectionTest) = True) Then
                Exit Sub
            End If


        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (ValidaCampos() = True) Then
                Exit Sub
            End If

            If Not (AlteraEspecieDoItem(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            If (VerificaDado(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (ExcluiEspecieDoItem(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnGravar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        Response.Redirect("EspecieItem.aspx")

    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

End Class
